--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Commit 2fb42d27fa658f31d11aa330071bab1766793496
Parents : 29b7fb6
Author : Sudo-Ivan <ivan@quad4.io>
Signature : Signature validation error
Date : 2026-01-14T11:01:50-06:00
Add pagination support in database queries for announces
- Implemented pagination at the database level for improved performance when no search query is provided.
- Added a new method `get_filtered_announces_count` in `AnnounceManager` to retrieve the total count of filtered announces for pagination.
- Adjusted the `ReticulumMeshChat` class to utilize the new count method and handle pagination more efficiently.
Changes
2 files changed, 71 insertions(+), 3 deletions(-)
Diff
diff --git a/meshchatx/meshchat.py b/meshchatx/meshchat.py
index 923cff38..01c54b42 100644
--- a/meshchatx/meshchat.py
+++ b/meshchatx/meshchat.py
@@ -5525,14 +5525,40 @@ class ReticulumMeshChat:
blocked_identity_hashes = [b["destination_hash"] for b in blocked]
# fetch announces from database
+ # If we don't have a search query, we can paginate at the database level
+ # which is much faster than fetching thousands of records and then paginating in Python.
+ db_limit = limit if not search_query else None
+ db_offset = offset if not search_query else 0
+
results = self.announce_manager.get_filtered_announces(
aspect=aspect,
identity_hash=identity_hash,
destination_hash=destination_hash,
query=None, # We filter in Python to support name search
blocked_identity_hashes=blocked_identity_hashes,
+ limit=db_limit,
+ offset=db_offset,
)
+ # fetch total count if we paginated in DB
+ total_count = 0
+ if not search_query:
+ # Get the count from the database for the same filters
+ # We should probably add a get_filtered_announces_count method to announce_manager
+ if db_limit is None:
+ total_count = len(results)
+ else:
+ # We need the total count for pagination to work in the frontend
+ total_count = self.announce_manager.get_filtered_announces_count(
+ aspect=aspect,
+ identity_hash=identity_hash,
+ destination_hash=destination_hash,
+ query=None,
+ blocked_identity_hashes=blocked_identity_hashes,
+ )
+
+ # ... rest of processing ...
+
# pre-fetch icons and other data to avoid N+1 queries in convert_db_announce_to_dict
other_user_hashes = [r["destination_hash"] for r in results]
user_icons = {}
@@ -5671,13 +5697,14 @@ class ReticulumMeshChat:
)
]
- # apply pagination
- total_count = len(all_announces)
- if offset is not None or limit is not None:
+ # Re-calculate total_count after search filter
+ total_count = len(all_announces)
+ # apply pagination after search
start = offset
end = start + (limit if limit is not None else total_count)
paginated_results = all_announces[start:end]
else:
+ # We already paginated in DB, and total_count was calculated before processing
paginated_results = all_announces
return web.json_response(
diff --git a/meshchatx/src/backend/announce_manager.py b/meshchatx/src/backend/announce_manager.py
index 173ccada..6c2e487f 100644
--- a/meshchatx/src/backend/announce_manager.py
+++ b/meshchatx/src/backend/announce_manager.py
@@ -89,3 +89,44 @@ class AnnounceManager:
params.extend([limit, offset])
return self.db.provider.fetchall(sql, params)
+
+ def get_filtered_announces_count(
+ self,
+ aspect=None,
+ identity_hash=None,
+ destination_hash=None,
+ query=None,
+ blocked_identity_hashes=None,
+ ):
+ sql = """
+ SELECT COUNT(*) as count
+ FROM announces a
+ LEFT JOIN contacts c ON (
+ a.identity_hash = c.remote_identity_hash OR
+ a.destination_hash = c.lxmf_address OR
+ a.destination_hash = c.lxst_address
+ )
+ WHERE 1=1
+ """
+ params = []
+
+ if aspect:
+ sql += " AND a.aspect = ?"
+ params.append(aspect)
+ if identity_hash:
+ sql += " AND a.identity_hash = ?"
+ params.append(identity_hash)
+ if destination_hash:
+ sql += " AND a.destination_hash = ?"
+ params.append(destination_hash)
+ if query:
+ like_term = f"%{query}%"
+ sql += " AND (a.destination_hash LIKE ? OR a.identity_hash LIKE ?)"
+ params.extend([like_term, like_term])
+ if blocked_identity_hashes:
+ placeholders = ", ".join(["?"] * len(blocked_identity_hashes))
+ sql += f" AND a.identity_hash NOT IN ({placeholders})"
+ params.extend(blocked_identity_hashes)
+
+ result = self.db.provider.fetchone(sql, params)
+ return result["count"] if result else 0
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────